﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace LearnCSharp
{
	class AdoNet_Excel
	{
		public static void Test()
		{
			LectureExcel();
			Console.WriteLine("\n");
			EcritureExcel();
			Console.WriteLine("\n");
			LectureExcel();
		}

		public static void LectureExcel()
		{
			string strFilePath				= Environment.CurrentDirectory + "\\Customers.xls";
			string strConnectionString		= "Provider=Microsoft.ACE.OleDb.12.0;Data Source=" + strFilePath + ";Extended Properties=\"Excel 12.0\";";
			OleDbConnection oConnection		= new OleDbConnection(strConnectionString);

			try
			{
				// Ouverture de la connexion
				oConnection.Open();

				// Récupération de la structure du fichier Ecel
				var oDataTable = oConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
				if (oDataTable == null)
					return;

				// Récupération des Feuilles du fichier Excel
				string[] arrExcelSheets = new String[oDataTable.Rows.Count];
				int i = 0;
				foreach (DataRow oRow in oDataTable.Rows)
				{
					arrExcelSheets[i] = oRow["TABLE_NAME"].ToString();
					i++;
				}
				if (arrExcelSheets.Length <= 0)
					return;

				// Commande de récupération de l'info
				string strCmdText		= "SELECT * FROM [" + arrExcelSheets[0] + "]";
				OleDbCommand oCommand	= new OleDbCommand(strCmdText, oConnection);
				OleDbDataReader oReader = oCommand.ExecuteReader();

				if (oReader.HasRows)
				{
					while (oReader.Read())
					{
						string strMessage = oReader[0].ToString();
						for(int r=1; r<oReader.FieldCount; r++)
						{
							strMessage += String.Format("\t{0}", oReader[r].ToString());
						}

						Console.WriteLine(strMessage);
					}
				}
			}
			catch (Exception e)
			{
				Console.WriteLine(e.Message);
			}
			finally
			{
				// Fermetture de la connection
				oConnection.Dispose();
			}
		}

		public static void EcritureExcel()
		{
			// Paramétrage de fichier et de connexion
			string strFilePath = Environment.CurrentDirectory + "\\Customers.xls";
			string strConnectionString = "Provider=Microsoft.ACE.OleDb.12.0;Data Source=" + strFilePath + ";Extended Properties=\"Excel 12.0\";";
			OleDbConnection oConnection = new OleDbConnection(strConnectionString);
			try
			{
				// Ouverture de connextion
				oConnection.Open();
				/*
				// Inserer des infos sur les colonnes "COL 1" et "COL 2"
				string strCmdText1 = "INSERT INTO [Feuil1$] ([COL 2], [COL 1]) VALUES ('Valeur dans la cellule A1', 'Autre valeur dans la cellule A2')";
				// Inserer des infos sur la 1er colonne et la deuxième
				string strCmdText2 = "INSERT INTO [Feuil1$] VALUES ('A1.2', 'A2.2')";
				// Modifier l'info de la colonne "COL 2" de la colonne "COL 1" de la ligne ayant l'info "A1.2"
				string strCmdText3 = "Update [Feuil1$] set [COL 2] = 'New Name' where [COL 1]='A1.2'";
				// Modifier toute les valeurs de la colonne "COL 2"
				string strCmdText4 = "Update [Feuil1$] set [COL 1] = '';";
				// Supprimer ligne
				//string strCmdText5 = "DELETE FROM [Feuil1$] WHERE [COL 1] = 'A1.2'"; // Impossible avec ADO				
				
				using (OleDbCommand command = new OleDbCommand(strCmdText1, oConnection))
				{
					command.ExecuteNonQuery();
				}

				using (OleDbCommand command = new OleDbCommand(strCmdText2, oConnection))
				{
					command.ExecuteNonQuery();
				}

				using (OleDbCommand command = new OleDbCommand(strCmdText3, oConnection))
				{
					command.ExecuteNonQuery();
				}

				using (OleDbCommand command = new OleDbCommand(strCmdText4, oConnection))
				{
					command.ExecuteNonQuery();
				}
								
				string strCmdText6 = "Update [Feuil1$] set [COL 2] = 'B2.New',[COL 1] = 'A2.New' where [COL 1]='A2'";
				using (OleDbCommand command = new OleDbCommand(strCmdText6, oConnection))
				{
					command.ExecuteNonQuery();
				}
				 
				string strCmdText7 = "Update [Feuil1$] set [COL 2] = 'B4.New' where [COL 1]='A4'";
				using (OleDbCommand command = new OleDbCommand(strCmdText7, oConnection))
				{
					if( command.ExecuteNonQuery() == 0 )
					{
						string strCmdText72 = "INSERT INTO [Feuil1$] VALUES ('A4', 'B4')";
						OleDbCommand command2 = new OleDbCommand(strCmdText72, oConnection);
						command2.ExecuteNonQuery();
					}
				}
				*/
				
				string strCmdText8 = "UPDATE [Feuil1$] SET [SUCCESS] = '2', [MISTAKE] = '2' WHERE [EN] = 'bread'";
				using (OleDbCommand command = new OleDbCommand(strCmdText8, oConnection))
				{
					command.ExecuteNonQuery();
				}
			}
			catch (Exception e)
			{
				Console.WriteLine(e.Message);
			}
			finally
			{
				// Fermetture de la connection
				oConnection.Dispose();
			}
		}

		public static void CreateFileExcel()
		{
			// Impossile avec Ado seulement avec Interop
		}

		public static void DeleteLineExcel()
		{
			// Impossile avec Ado seulement avec Interop
		}

	}
}
